using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using PHSRAG.Insight21.BusinessObjects;
using ICachePolicy = PHSRAG.Insight21.Policy.ICachePolicy;
using PHSRAG.Utility;

namespace PHSRAG.Insight21.ClinicalTrials
{
   /// <summary>
   /// The BudgetStaffList class represents a collection of BudgetStaff objects.  It is 
   /// responsible for loading and saving the Budget Staff objects
   /// </summary>
   public class BudgetStaffList : SaveableBusinessObject, IEnumerable
   {

      #region Constants
      private const string BudgetStaffListKey = "BudgetStaffList_DB8CF5AE-FD82-4c54-82A3-29DB33D47B4D";
      private const string TableName = "BudgetStaffList";
      #endregion

      #region Instance Variables
      private ArrayList budgetStaff = new ArrayList( );
      private Budget budget;
      private SqlCommand updateBudgetStaffCommand;
      private SqlCommand deleteBudgetStaffCommand;
      #endregion

      #region Constructor
      /// <summary>
      /// Initialize the base class with the specified cache policy and create the data container.
      /// </summary>
      public BudgetStaffList( ICachePolicy cachePolicy ) : base ( cachePolicy )
      {
         DataContainer = new DataSet( );
         budget = (Budget)CachePolicy.GetCachedObject( Budget.CacheKey );
      }
      #endregion
   
      #region Base Methods
      /// <summary>
      /// Cache this instance using the prevalent cache policy.
      /// </summary>
      public override void Cache( )
      {
         CachePolicy.Cache( BudgetStaffListKey, this );
      }

      /// <summary>
      /// Remove this instance from the cache using the prevalent cache policy.
      /// </summary>
      public override void UnCache( )
      {
         CachePolicy.UnCache( BudgetStaffListKey );
      }

      /// <summary>
      /// Load staff details pertaining to a specific budget.
      /// </summary>
      /// <param name="connectionString">Connection string to the database</param>
      /// <param name="args">Object array which must contain the budget ID.</param>
      public override void Load( string connectionString, params object[] args )
      {
         if ( args.Length != 1 )
            throw new Exception("BudgetStaffList load requires a budget id");
			
         DataAccess.LoadDataSet( DataContainer, TableName, connectionString, "GetClinicalTrialBudgetStaff", 
            Utility.DataAccess.BuildSqlParameterArray( "@budgetId",SqlDbType.Int,args[0] ) );

         if ( DataContainer.Tables.Count == 0 )
            throw new Exception("Failed to load Budget Staff information.");

         foreach( DataRow budgetStaffRow in DataContainer.Tables[TableName].Rows )
         {
            budgetStaff.Add( new BudgetStaff( CachePolicy, budgetStaffRow ) );
         }
      }

      /// <summary>
      /// Saves staff details associated with this budget.  
      /// </summary>
      /// <param name="connectionString">Connection string to the database</param>
      /// <param name="args">Optional parameters (ignored)</param>
      public override void Save( string connectionString, params object[] args )
      {
         SqlConnection connection = new SqlConnection( connectionString );
         SqlTransaction transaction = null;
         try
         {
            connection.Open( );
            transaction = connection.BeginTransaction( );
            BuildCommands( );
            updateBudgetStaffCommand.Connection = connection;
            updateBudgetStaffCommand.Transaction = transaction;
            deleteBudgetStaffCommand.Connection = connection;
            deleteBudgetStaffCommand.Transaction = transaction;

            SqlDataAdapter adapter = new SqlDataAdapter( );
            adapter.InsertCommand = updateBudgetStaffCommand;
            adapter.UpdateCommand = updateBudgetStaffCommand;
            adapter.DeleteCommand = deleteBudgetStaffCommand;
            adapter.Update( DataContainer, TableName );

            transaction.Commit( );
         }
         catch( Exception e )
         {
            transaction.Rollback( );
            throw(new Exception(@"Error saving Budget Staff to the database.", e));
         }
         finally
         {
            if ( connection.State != ConnectionState.Closed )
               connection.Close( );
            transaction.Dispose( );
         }
      }
      #endregion

      #region Private Methods
      private void BuildCommands( )
      {
         if ( updateBudgetStaffCommand == null )
         {
            updateBudgetStaffCommand = new SqlCommand( );
            updateBudgetStaffCommand.CommandText = "UpdateClinicalTrialBudgetStaff";
            updateBudgetStaffCommand.CommandType = CommandType.StoredProcedure;
			updateBudgetStaffCommand.CommandTimeout = Convert.ToInt32(Utility.Settings.GetKeyValue("Database.Insight.CommandTimeout", "0"));
			SqlParameter paramId = new SqlParameter( "@budgetStaffId", SqlDbType.Int );
            paramId.SourceColumn = "Id";
            paramId.Direction = ParameterDirection.InputOutput;
            updateBudgetStaffCommand.Parameters.Add( paramId );
            updateBudgetStaffCommand.Parameters.Add( "@budgetId", SqlDbType.Int ).SourceColumn = "ClinicalTrialBudgetId";
            updateBudgetStaffCommand.Parameters.Add( "@studyStaffRoleCode", SqlDbType.VarChar ).SourceColumn = "StudyStaffRoleCode";
            updateBudgetStaffCommand.Parameters.Add( "@otherRoleText", SqlDbType.VarChar ).SourceColumn = "OtherRoleText";
            updateBudgetStaffCommand.Parameters.Add( "@Rate", SqlDbType.Decimal ).SourceColumn = "Rate";
         }

         if ( deleteBudgetStaffCommand == null )
         {
            deleteBudgetStaffCommand = new SqlCommand( );
            deleteBudgetStaffCommand.CommandText = "DeleteClinicalTrialBudgetStaff";
            deleteBudgetStaffCommand.CommandType = CommandType.StoredProcedure;
			deleteBudgetStaffCommand.CommandTimeout = Convert.ToInt32(Utility.Settings.GetKeyValue("Database.Insight.CommandTimeout", "0"));
			SqlParameter paramDeleteId = new SqlParameter( "@budgetStaffId", SqlDbType.Int );
            paramDeleteId.SourceColumn = "Id";
            paramDeleteId.Direction = ParameterDirection.Input;
            deleteBudgetStaffCommand.Parameters.Add( paramDeleteId );
         }
      }
      #endregion

      #region IEnumerable Methods
      /// <summary>
      /// Gets an IEnumerator interface.  Required for implementation of IEnumerable
      /// </summary>
      /// <returns>IEnumerator</returns>
      public IEnumerator GetEnumerator( )
      {
		   return budgetStaff.GetEnumerator( );
      }
      #endregion

      #region Public Collection helpers
      /// <summary>
      /// The Count property returns the number of items in the Budget Staff collection
      /// </summary>
      public int Count
      {
         get
         {
            return budgetStaff.Count;
         }
      }

      /// <summary>
      /// Indexer to get a specific Budget Staff from the collection
      /// </summary>
      public BudgetStaff this[int index]
      {
         get
         {
            return (BudgetStaff)budgetStaff[ index ];
         }
      }

      /// <summary>
      /// Gets a budget staff from the collection by Staff Id.  Used by the BudgetTaskControl
      /// to get the rate associated with a staff.
      /// </summary>
      /// <param name="budgetStaffId"></param>
      /// <returns>BudgetStaff</returns>
      public BudgetStaff GetStaffById( int budgetStaffId )
      {
         BudgetStaff returnVal = null;
         foreach( BudgetStaff staffIterator in budgetStaff )
         {
            if ( staffIterator.Id == budgetStaffId )
            {
               returnVal = staffIterator;
               break;
            }
         }
         return returnVal;
      }

      /// <summary>
      /// Gets the first budget staff from the collection by role code.  Used when setting
      /// up default tasks upon first save of the budget
      /// </summary>
      /// <param name="roleCode"></param>
      /// <returns></returns>
      public BudgetStaff GetStaffByRoleCode( string roleCode )
      {
         BudgetStaff returnVal = null;
         foreach( BudgetStaff staffIterator in budgetStaff )
         {
            if ( staffIterator.StudyStaffRoleCode == roleCode )
            {
               returnVal = staffIterator;
               break;
            }
         }
         return returnVal;
      }
      #endregion

      #region Public Methods
      /// <summary>
      /// Creates a new BudgetStaff and adds the item to the collection.  Also sets default
      /// properties on the BudgetStaff.
      /// </summary>
      /// <returns>BudgetStaff</returns>
      public BudgetStaff CreateNewBudgetStaff( )
      {
         DataRow newRow = DataContainer.Tables[TableName].NewRow( );
         DataContainer.Tables[TableName].Rows.Add( newRow );
         BudgetStaff newBudgetStaff = new BudgetStaff( CachePolicy, newRow );
         newBudgetStaff.BudgetId = budget.Id;
         budgetStaff.Add( newBudgetStaff );
		   return newBudgetStaff;
      }

      /// <summary>
      /// Removes a specific BudgetStaff from the collection.  Must call save on BudgetStaffList
      /// to save this change to the database. However this may not be the case if we are deleting an 
      /// incomplete staff then we don't need to go back to the database and just remove it from the 
      /// collection 
      /// </summary>
      /// <param name="budgetStaff">BudgetStaff to remove from the collection.</param>
      public void RemoveBudgetStaff( BudgetStaff budgetStaff )
      {
		  if(budgetStaff.Id == 0)
		  {		  
			  DataContainer.Tables[TableName].Rows.RemoveAt(  DataContainer.Tables[TableName].Rows.Count - 1 );
		  }
		  else
		  {			 
			  DataContainer.Tables[TableName].Select( string.Format( "Id = {0}", budgetStaff.Id ) )[0].Delete( ); 
		  }
		  this.budgetStaff.Remove( budgetStaff );
      }
      #endregion
   
   }// END CLASS DEFINITION BudgetStaffList
}